package com.map;

import org.apache.http.HttpEntity;
import org.apache.http.HttpStatus;
import org.apache.http.client.config.RequestConfig;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import sun.misc.BASE64Encoder;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Base64;
import java.util.UUID;

/**
 * Description: load
 * <p>
 * Created by 韩博 on 2020/4/26 11:22
 */
public class SqliteHelper {
    /**
     * 数据库路径
     */
    private String path;
    Connection c;
    Statement stmt;
    private  static final String URL="https://mt1.google.cn/vt/lyrs=s@113&hl=nl&x={x}&y={y}&z={z}&s=";


    public SqliteHelper(String path) {
        this.path = path;
        try {
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite:" + path);
            System.out.println("Opened database successfully");
            stmt = c.createStatement();
            initTable();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    private void initTable() throws SQLException {
        String sql = "PRAGMA foreign_keys = false;\n" +
                "DROP TABLE IF EXISTS \"tiles\";\n" +
                "CREATE TABLE \"tiles\" (\n" +
                "  \"zoom_level\" integer,\n" +
                "  \"tile_column\" integer,\n" +
                "  \"tile_row\" integer,\n" +
                "  \"tile_data\" blob\n" +
                ");\n" +
                "\n" +
                "CREATE UNIQUE INDEX \"tile_index\"\n" +
                "ON \"tiles\" (\n" +
                "  \"zoom_level\" ASC,\n" +
                "  \"tile_column\" ASC,\n" +
                "  \"tile_row\" ASC\n" +
                ");\n" +
                "\n" +
                "PRAGMA foreign_keys = true;";
        stmt.executeUpdate(sql);
    }
//    private void initTable() throws SQLException {
//        String sql = "PRAGMA foreign_keys = false;\n" +
//                "DROP TABLE IF EXISTS \"images\";\n" +
//                "CREATE TABLE \"images\" (\n" +
//                "  \"tile_data\" BLOB,\n" +
//                "  \"tile_id\" TEXT\n" +
//                ");\n" +
//                "\n" +
//                "CREATE UNIQUE INDEX \"images_id\"\n" +
//                "ON \"images\" (\n" +
//                "  \"tile_id\" ASC\n" +
//                ");\n" +
//                "\n" +
//                "PRAGMA foreign_keys = true;\n";
//        stmt.executeUpdate(sql);
//        sql = "PRAGMA foreign_keys = false;\n" +
//                "DROP TABLE IF EXISTS \"map\";\n" +
//                "CREATE TABLE \"map\" (\n" +
//                "  \"zoom_level\" INTEGER,\n" +
//                "  \"tile_column\" INTEGER,\n" +
//                "  \"tile_row\" INTEGER,\n" +
//                "  \"tile_id\" TEXT\n" +
//                ");\n" +
//                "CREATE UNIQUE INDEX \"map_index\"\n" +
//                "ON \"map\" (\n" +
//                "  \"zoom_level\" ASC,\n" +
//                "  \"tile_column\" ASC,\n" +
//                "  \"tile_row\" ASC\n" +
//                ");\n" +
//                "PRAGMA foreign_keys = true;";
//        stmt.executeUpdate(sql);
//        sql = "PRAGMA foreign_keys = false;\n" +
//                "DROP TABLE IF EXISTS \"metadata\";\n" +
//                "CREATE TABLE \"metadata\" (\n" +
//                "  \"name\" nvarchar(64),\n" +
//                "  \"value\" TEXT,\n" +
//                "  PRIMARY KEY (\"name\")\n" +
//                ");\n" +
//                "CREATE UNIQUE INDEX \"name\"\n" +
//                "ON \"metadata\" (\n" +
//                "  \"name\" ASC\n" +
//                ");\n" +
//                "\n" +
//                "PRAGMA foreign_keys = true;";
//        stmt.executeUpdate(sql);
//    }

    /**
     * 关闭SQLite
     */
    public void close() {
        try {
            if (stmt != null) {
                stmt.close();
                c.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 发送get请求,  下载图片
     *
     * @param
     *
     * @return
     */
    public  void httpGetImg(CloseableHttpClient client, TileIndex tileIndex ) {
        String imgUrl=URL.replace("{x}",String.valueOf(tileIndex.getX()))
                .replace("{y}",String.valueOf(tileIndex.getY()))
                .replace("{z}",String.valueOf(tileIndex.getZ()));

        // 发送get请求
        HttpGet request = new HttpGet(imgUrl);
        // 设置请求和传输超时时间
        RequestConfig requestConfig = RequestConfig.custom()
                .setSocketTimeout(50000).setConnectTimeout(50000).build();

        //设置请求头
        request.setHeader( "User-Agent","Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.79 Safari/537.1" );

        request.setConfig(requestConfig);
        try {
            CloseableHttpResponse response = client.execute(request);

            if (HttpStatus.SC_OK == response.getStatusLine().getStatusCode()) {
                HttpEntity entity = response.getEntity();

                InputStream in = entity.getContent();

                int ch;
                ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
                byte[] b=null;
                while ((ch = in.read()) != -1) {
                    byteArrayOutputStream.write(ch);
                }
                b= byteArrayOutputStream.toByteArray();
                byteArrayOutputStream.close();
               insertTile(b,tileIndex.getX(),tileIndex.getY(),tileIndex.getZ());

            }

        } catch (IOException | SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            request.releaseConnection();

        }
    }

    public  void insertTile(byte[] img,Integer x,Integer y,Integer z) throws SQLException {
        String uuid= UUID.randomUUID().toString().replaceAll("-","");
//        BASE64Encoder encoder=new BASE64Encoder();
//        String s= encoder.encode(img);
//        s=" \"$binary\" : \""+s+"\", \"$type\" : \"00\"";
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(img);
        PreparedStatement pstmt = null;
        String sql="insert into tiles (zoom_level,tile_column,tile_row,tile_data) values ("+z+","+x+","+y+",?)";
        pstmt = c.prepareStatement(sql);
        pstmt.setBinaryStream(1, byteArrayInputStream, byteArrayInputStream.available());
        pstmt.executeUpdate();
    }

}
